package com.tskj.leonsu.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.tskj.leonsu.db.DbUtilitys;
import com.tskj.leonsu.db.redis.util.RedisUtil;
import com.tskj.leonsu.utility.ModuleType;
import com.tskj.leonsu.utility.Tools;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.*;

@Component
public class MealsDAO {

    private final String MODULE_TYPE = String.valueOf(ModuleType.DING_CAN.ordinal());

    @Autowired
    private DbUtilitys dbUtilitys;

    @Autowired
    private RedisUtil redisUtil;

    public JSONArray getRestaurantList() {
        String sql = "select cid id, canteenname text from canteen where used=1 order by canteenname";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public boolean getCanteenStatus() {
        String sql = "select isactive from modules where id=4";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql);
        if (list == null || list.size() == 0) {
            return false;
        }
        return Boolean.valueOf(list.get(0).get("isactive").toString());
    }

    public JSONArray getTimeTypeList() {
        String sql = "select sid id, dinnername title from dinner order by sortno";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 从数据库中获取菜单信息
     * 包括了食堂名称，食堂的排序序号，餐段名称
     * 食堂的排序序号用于生成取餐码
     *
     * @return
     */
    public List<Map<String, Object>> getList() {
        String sql = "select mid, mealname, mdetails, photo, money, meals.cid cid, canteenname, canteen_rank_no, dinner.sid sid, dinnername from meals " +
                "left join (select a.*,@rank:=@rank + 1 AS canteen_rank_no " +
                "from (select * from canteen " +
                "order by canteenname) a, (select @rank:= 0) b) c on meals.cid=c.cid " +
                "left join dinner on meals.sid=dinner.sid";
        return dbUtilitys.execSQL(sql);
    }

    /**
     * 从数据库中获取菜单信息
     * 包括了食堂名称，食堂的排序序号，餐段名称
     * 食堂的排序序号用于生成取餐码
     *
     * @param resId      食堂id
     * @param timeTimeId 餐段id
     * @return
     */
    public JSONArray getList(String resId, String timeTimeId) {
        JSONArray retList = new JSONArray();
        Map<Object, Object> map = redisUtil.hmget("meals");
        Set<Object> keys = map.keySet();
        JSONObject data;
        for (Object key : keys) {
            data = JSONObject.parseObject(map.get(key).toString());
            if (data.getString("cid").equalsIgnoreCase(resId) && data.getString("sid").equalsIgnoreCase(timeTimeId)) {
                retList.add(data);
            }
        }
        return retList;
    }

    /**
     * 将菜单立即写入到redis中
     *
     * @return
     */
    public boolean writeFoodListToRedisNow() {
        List<Map<String, Object>> list = getList();
        redisUtil.del("meals");
        for (Map<String, Object> map : list) {
            String mealId = map.get("mid").toString();

//            if (!redisUtil.hHasKey("meals", mealId)) {
//                redisUtil.hdel("meals", mealId);
//            }
            redisUtil.hset("meals", mealId, JSONObject.parse(JSONObject.toJSONString(map)));
        }
        return true;
    }

    /**
     * 获取未完成订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getUnFinishedOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, mname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, '' xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate, " +
//                "1 canCancel, " +
                "date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now() canCancel, " +
                "str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now() canConfirm " +
                "from meal_order " +
                "where createdate>=DATE_SUB(date(now()), INTERVAL 5 day) and adusername=? and orderstate=0 " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取已完成订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getFinishedOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, mname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, '' xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from meal_order " +
                "where createdate>=DATE_SUB(date(now()), INTERVAL 5 day) and adusername=? and orderstate=1 " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取全部订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getAllOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, mname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, '' xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from meal_order " +
                "where createdate>=DATE_SUB(date(now()), INTERVAL 5 day) and adusername=? " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取订单是否允许确认
     *
     * @param orderId
     * @return 0：不能确认，1：可以确认
     */
    public Boolean getCanConfirm(String orderId) {
        String sql = "select str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now() canConfirm from meal_order where orderid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, orderId);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("canConfirm");
        if (value == null || !"1".equals(value.toString())) {
            return false;
        }
        return true;

    }

    public void confirmOrder(String adUserName, String orderId) throws Exception {
        String sql = "update meal_order set orderstate=1, ctime=now() where orderid=? and adusername=? and str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now()";
        dbUtilitys.execSQLWithTran(sql, orderId, adUserName);
    }

    /**
     * 获取订单是否允许取消
     *
     * @param orderId
     * @return 0：不能取消，1：可以取消
     */
    public Boolean getCanCancel(String orderId) {
        String sql = "select date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now() canCancel from meal_order where orderid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, orderId);
//        if (list == null || list.size() == 0) {
//            return false;
//        }
//        Object value = list.get(0).get("canCancel");
//        if (value == null || !"1".equals(value.toString())) {
//            return false;
//        }
        return true;

    }

    public void cancelOrder(String adUserName, String orderId) throws Exception {
        String sql = "update meal_order set orderstate=2, ctime=now() where orderid=? and adusername=?";// and date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now()";
        dbUtilitys.execSQLWithTran(sql, orderId, adUserName);
    }

    private List<Map<String, Object>> getCanteenList() {
        String sql = "select a.* ,@rank:=@rank + 1 AS sortnum " +
                "from (select * from canteen " +
                "order by canteenname) a, (select @rank:= 0) b";
        return dbUtilitys.execSQL(sql);
    }

    public void writeCanteenToRedis() {
        List<Map<String, Object>> list = getCanteenList();
        for (Map<String, Object> map : list) {
            String mealId = map.get("mid").toString();

            if (!redisUtil.hHasKey("meals", mealId)) {
                redisUtil.hdel("meals", mealId);
            }
            redisUtil.hset("meals", mealId, JSONObject.parse(JSONObject.toJSONString(map)));
        }
    }

    public void addOrderCount(String date, String timeId) {
        //队列加1
        String key = date + "_" + timeId;
        redisUtil.lSet(key, 1);
        long size = redisUtil.lGetListSize(key);
        JSONObject timeInfo = JSONObject.parseObject(redisUtil.hget(date, timeId).toString());
        timeInfo.put("used", size);
        redisUtil.hset(date, timeId, timeInfo);
    }

    public List<Map<String, Object>> getTimeList() {
        String sql;
        //"select persionid, cid, sid, starttime, endtime, tm_num, time_rank_no, tm_num used from (" +
//                "select a.*, @rank:=@rank+1 AS time_rank_no, " +
//                "select a.*, case when @pre_cid = a.cid and @pre_sid = a.sid then @rank:= @rank + 1 else @rank:= 2 end AS time_rank_no, " +
//                "(@pre_cid:=a.cid), (@pre_sid:=a.sid) " +
//                "from (select * from reserve_time " +
//                "order by cid,sid,starttime) a, (select @rank:= 0, @pre_cid:='', @pre_sid='') b " +
//                "order by cid,sid,starttime";
        sql = "select a.persionid id, a.starttime begin, a.endtime end, a.sid timetypeid, a.tm_num count, a.cid resid, count(1) as time_rank_no " +
                "from reserve_time a " +
                "left join reserve_time b on a.cid=b.cid and a.sid=b.sid and a.starttime>=b.starttime " +
                "group by " +
                "a.cid, a.sid, a.starttime " +
                "order by " +
                "a.cid, a.sid, a.starttime";
        return dbUtilitys.execSQL(sql);
//        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public JSONObject getOrderInfo(String orderId) {
        String sql = "select meal_order.*, canteen.cid from meal_order left join canteen on canteenname = cname where orderid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, orderId);
        return JSONObject.parseObject(JSONObject.toJSONString(list.get(0)));
    }

    public void writeTimeListToRedis() {
        List<Map<String, Object>> list = getTimeList();
        String today = Tools.today();
//        System.out.println(list.toJSONString());
        for (Map<String, Object> map : list) {
            String timeId = map.get("id").toString();
            map.put("used", map.get("count"));
            if (!redisUtil.hHasKey(today, timeId)) {
                redisUtil.hdel(today, timeId);
            }
            redisUtil.hset(today, timeId, JSONObject.parse(JSONObject.toJSONString(map)));
        }
    }

    public List<String> getTimeListInRedis(String date, String resId, String timeTypeId) {
        List<String> retList = new ArrayList<>();
        Map<Object, Object> allData = redisUtil.hmget(date);
        Set<Object> keys = allData.keySet();
        JSONObject data;
        for (Object key : keys) {
            data = JSONObject.parseObject(allData.get(key).toString());
            if (data.getString("resid").equalsIgnoreCase(resId) && data.getString("timetypeid").equalsIgnoreCase(timeTypeId)) {
                retList.add(data.getString("begin"));
            }
        }
        return retList;
    }

    public String getTimeId(String date, String resId, String startTime, String endTime) {
        Map<Object, Object> allData = redisUtil.hmget(date);
        Set<Object> keys = allData.keySet();
        JSONObject data;
        for (Object key : keys) {
            data = JSONObject.parseObject(allData.get(key).toString());
            if (data.getString("resid").equalsIgnoreCase(resId) && data.getString("begin").equalsIgnoreCase(startTime) && data.getString("end").equalsIgnoreCase(endTime)) {
                return data.getString("id");
            }
        }
        return "";
    }

    public String getCanteenName(String resId) {
        String sql = "select canteenname from canteen where cid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, resId);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object value = list.get(0).get("canteenname");
        if (value == null) {
            return "";
        }
        return value.toString();
    }

    public Boolean checkHasOrder(String adUserName, String date, String resId, String timeTypeId) {
        String resName = getCanteenName(resId);
        if (resName.isEmpty()) {
            return false;
        }
        List<String> startTime = getTimeListInRedis(date, resId, timeTypeId);
        String sql = "select ifnull(count(*), 0) count from meal_order where adusername=? and startdate=? and cname=? and FIND_IN_SET (starttime, ?) and orderstate=0";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, date, resName, StringUtils.join(startTime, ","));
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("count");
        if (value == null || !"0".equals(value.toString())) {
            return false;
        }
        return true;
    }
}
